﻿using System;
using System.Data;
using System.Data.SqlClient;
using AccessControl.Common;

namespace AccessControl.DataAcess
{
    public class EventLogAccess
    {
        public static AccessControlDS.tblEventLogDataTable SelectAll()
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdSelect = new SqlCommand("SELECT * FROM tblEventLog", cn);
                var tblEventLogTable = new AccessControlDS.tblEventLogDataTable();
                try
                {
                    cn.Open();
                    var reader = cmdSelect.ExecuteReader();
                    while (reader.Read())
                    {
                        var newRow = tblEventLogTable.NewtblEventLogRow();
                        newRow.pkEventLog = (int)reader["pkEventLog"];
                        newRow.fkPerson = (String)reader["fkPerson"];
                        newRow.fkWorkflow = (int)reader["fkWorkflow"];
                        newRow.DateRegistered = (DateTime)reader["DateRegistered"];
                        newRow.Reason = Convert.ToString(reader["Reason"]);
                        newRow.Result = (byte)reader["Result"];
                        tblEventLogTable.AddtblEventLogRow(newRow);
                    }
                }
                finally
                {
                    cn.Close();
                }
                return tblEventLogTable.Rows.Count != 0 ? tblEventLogTable : null;
            }
        }

        public static object GetLastEventDate(string pkPerson, int pkEvent)
        {

            using (var cn = new SqlConnection(Util.ConnectionString)){
                try{
                    cn.Open();
                    var cmdSelect = new SqlCommand("SELECT MAX(DateRegistered) FROM tblEventLog INNER JOIN tblWorkflow ON pkWorkflow=fkWorkflow WHERE fkEvent=@fkEvent AND fkPerson=@fkPerson", cn);
                    cmdSelect.Parameters.Add("@fkEvent", SqlDbType.Int).Value = pkEvent;
                    cmdSelect.Parameters.Add("@fkPerson", SqlDbType.Int).Value = pkPerson;
                    return cmdSelect.ExecuteScalar();
                }
                catch (Exception){
                    return null;
                }finally{
                    cn.Close();
                }
            }
        }

        public static AccessControlDS.tblEventLogDataTable SelectByFilter(string filter)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdSelect = new SqlCommand(String.Format("SELECT * FROM tblEventLog WHERE {0}", filter), cn);
                var tblEventLogTable = new AccessControlDS.tblEventLogDataTable();
                try
                {
                    cn.Open();
                    var reader = cmdSelect.ExecuteReader();
                    while (reader.Read())
                    {
                        var newRow = tblEventLogTable.NewtblEventLogRow();
                        newRow.pkEventLog = (long)reader["pkEventLog"];
                        newRow.fkPerson = (String)reader["fkPerson"];
                        newRow.fkWorkflow = (int)reader["fkWorkflow"];
                        newRow.DateRegistered = (DateTime)reader["DateRegistered"];
                        newRow.Reason = Convert.ToString(reader["Reason"]);
                        newRow.Result = (byte)reader["Result"];
                        tblEventLogTable.AddtblEventLogRow(newRow);
                    }
                }
                finally
                {
                    cn.Close();
                }
                return tblEventLogTable.Rows.Count != 0 ? tblEventLogTable : null;
            }
        }

        public static DataTable SelectByFilterForReport(string filter, int groupType, DateTime startDate, DateTime endDate)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdSelect = new SqlCommand(String.Format("SELECT tblPerson.PersonRefNumber,tblPerson.PersonFirstName,tblPerson.PersonLastName, tblWorkflow.WorkflowName,tblEventLog.Reason,tblEventLog.Result, tblEventLog.DateRegistered,tblEvent.EventName,tblEventType.EventTypeName,tblGroup.GroupName FROM tblEventLog INNER JOIN tblWorkflow ON tblWorkflow.pkWorkflow=tblEventLog.fkWorkflow INNER JOIN tblPerson ON tblPerson.pkPerson=tblEventLog.fkPerson INNER JOIN tblEvent ON tblWorkflow.fkEvent=tblEvent.pkEvent INNER JOIN tblEventType ON tblEvent.fkEventType=tblEventType.pkEventType LEFT JOIN tblPersonGroup INNER JOIN tblGroup ON tblPersonGroup.fkGroup=tblGroup.pkGroup AND tblPersonGroup.fkGroupType=@GroupType AND tblPersonGroup.fkGroupType=tblGroup.fkGroupType ON tblPerson.pkPerson= tblPersonGroup.fkPerson WHERE tblEventLog.DateRegistered >= @StartDate AND tblEventLog.DateRegistered<= @EndDate {0}", filter), cn);
                cmdSelect.Parameters.AddWithValue("@StartDate", startDate);
                cmdSelect.Parameters.AddWithValue("@EndDate", endDate);
                cmdSelect.Parameters.AddWithValue("@GroupType", groupType);
                var tblEventLogTable = new DataTable();
                try
                {
                    cn.Open();
                    var tableAdapter = new SqlDataAdapter(cmdSelect);
                    tableAdapter.Fill(tblEventLogTable);
                }
                catch (Exception ex)
                {
                }
                finally
                {
                    cn.Close();
                }
                return tblEventLogTable.Rows.Count != 0 ? tblEventLogTable : null;
            }
        }

        public static DataTable SelectByFilterForReportDistinct(string filter, int groupType, DateTime startDate, DateTime endDate)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdSelect = new SqlCommand(String.Format("SELECT tblPerson.PersonRefNumber,tblPerson.PersonFirstName,tblPerson.PersonLastName, tblWorkflow.WorkflowName,tblEventLog.Reason,tblEventLog.Result, tblEventLog.DateRegistered,tblEvent.EventName,tblEventType.EventTypeName,tblGroup.GroupName FROM tblEventLog INNER JOIN tblWorkflow ON tblWorkflow.pkWorkflow=tblEventLog.fkWorkflow INNER JOIN tblPerson ON tblPerson.pkPerson=tblEventLog.fkPerson INNER JOIN tblEvent ON tblWorkflow.fkEvent=tblEvent.pkEvent INNER JOIN tblEventType ON tblEvent.fkEventType=tblEventType.pkEventType INNER JOIN (SELECT MAX(pkEventLog) AS pkLastEventLog FROM tblEventLog WHERE DateRegistered >= @StartDate AND DateRegistered<= @EndDate GROUP BY fkPerson) AS LastEventLog ON LastEventLog.pkLastEventLog = pkEventLog LEFT JOIN tblPersonGroup INNER JOIN tblGroup ON tblPersonGroup.fkGroup=tblGroup.pkGroup AND tblPersonGroup.fkGroupType=@GroupType AND tblPersonGroup.fkGroupType=tblGroup.fkGroupType ON tblPerson.pkPerson= tblPersonGroup.fkPerson WHERE tblEventLog.DateRegistered >= @StartDate AND tblEventLog.DateRegistered<= @EndDate {0}", filter), cn);
                cmdSelect.Parameters.AddWithValue("@StartDate", startDate);
                cmdSelect.Parameters.AddWithValue("@EndDate", endDate);
                cmdSelect.Parameters.AddWithValue("@GroupType", groupType);
                var tblEventLogTable = new DataTable();
                try
                {
                    cn.Open();
                    var tableAdapter = new SqlDataAdapter(cmdSelect);
                    tableAdapter.Fill(tblEventLogTable);
                }
                catch (Exception ex)
                {
                }
                finally
                {
                    cn.Close();
                }
                return tblEventLogTable.Rows.Count != 0 ? tblEventLogTable : null;
            }
        }

        public static void Insert(AccessControlDS.tblEventLogRow row)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdInsert = new SqlCommand("spEventLog_Insert", cn) { CommandType = CommandType.StoredProcedure };
                cmdInsert.Parameters.AddWithValue("@fkPerson", row.fkPerson);
                cmdInsert.Parameters.AddWithValue("@fkWorkflow", row.fkWorkflow);
                cmdInsert.Parameters.AddWithValue("@Reason", row.Reason);
                if (!row.IsfkGuestNull()) cmdInsert.Parameters.AddWithValue("@fkGuest", row.fkGuest);
                cmdInsert.Parameters.AddWithValue("@Result", row.Result);
                cmdInsert.Parameters.AddWithValue("@DateRegistered", row.DateRegistered);
                try
                {
                    cn.Open();
                    row.pkEventLog = Convert.ToInt64(cmdInsert.ExecuteScalar());
                }
                finally
                {
                    cn.Close();
                }
            }
        }

        public static void Update(AccessControlDS.tblEventLogRow row)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdUpdate = new SqlCommand("spEventLog_Update", cn) { CommandType = CommandType.StoredProcedure };
                cmdUpdate.Parameters.AddWithValue("@pkEventLog", row.pkEventLog);
                cmdUpdate.Parameters.AddWithValue("@fkPerson", row.fkPerson);
                cmdUpdate.Parameters.AddWithValue("@fkWorkflow", row.fkWorkflow);
                if (!row.IsfkGuestNull()) cmdUpdate.Parameters.AddWithValue("@fkGuest", row.fkGuest);
                cmdUpdate.Parameters.AddWithValue("@Reason", row.Reason);
                cmdUpdate.Parameters.AddWithValue("@Result", row.Result);
                cmdUpdate.Parameters.AddWithValue("@DateRegistered", row.DateRegistered);
                try
                {
                    cn.Open();
                    cmdUpdate.ExecuteNonQuery();
                }
                finally
                {
                    cn.Close();
                }
            }
        }

        public static void Delete(AccessControlDS.tblEventLogRow row)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdDelete = new SqlCommand("spEventLog_Delete", cn) { CommandType = CommandType.StoredProcedure };
                cmdDelete.Parameters.AddWithValue("@pkEventLog", row.pkEventLog);
                try
                {
                    cn.Open();
                    cmdDelete.ExecuteNonQuery();
                }
                finally
                {
                    cn.Close();
                }
            }
        }

        public static bool HasRecord(string pkPerson)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdCount = new SqlCommand(String.Format("SELECT COUNT(*) from tblEventLog WHERE fkPerson=@fkPerson", pkPerson), cn) { CommandType = CommandType.Text };
                cmdCount.Parameters.AddWithValue("@fkPerson", pkPerson);
                try
                {
                    cn.Open();
                    var count =(int) cmdCount.ExecuteScalar();
                    return count > 0;

                }
                catch (Exception)
                {
                    return false;
                }
                finally
                {
                    cn.Close();
                }
            }
        }

        public static bool HasTwinEvent(Int64 pkEventLog)
        {
            using (var cn = new SqlConnection(Util.ConnectionString))
            {
                var cmdCount = new SqlCommand(String.Format("SElECT ISNULL(tblEvent.fkTwinEvent,0) FROM tblEventLog INNER JOIN tblWorkflow ON tblEventLog.fkWorkflow=tblWorkflow.pkWorkflow INNER JOIN tblEvent ON tblWorkflow.fkEvent=tblEvent.pkEvent WHERE tblEventLog.pkEventLog=@pkEventLog"), cn) { CommandType = CommandType.Text };
                cmdCount.Parameters.AddWithValue("@pkEventLog", pkEventLog);
                try
                {
                    cn.Open();
                    var count = (int)cmdCount.ExecuteScalar();
                    return count != 0;
                }
                catch (Exception ex)
                {
                    throw new ApplicationException(ex.Message);
                }
                finally
                {
                    cn.Close();
                }
            }
        }
    }
}
